Release 10.1A: OpenEdge Getting Started:
Database Essentials

Data area optimization

Following a few simple rules can make your data areas easy to maintain and provide optimal performance for users.

Splitting off the schema

By splitting off the schema and sequences from other portions of your database, you can make future upgrades to OpenEdge and your application more transparent to the user. One of the operations that takes place during most major version changes of OpenEdge is a metaschema change. The metaschema is a set of definitions for the internal structure of data. Your schema definitions create the rules by which data can be added or modified within your application. Metaschema serves the same purpose for the definition of your schema. By eliminating information other than your schema definitions and sequences from the metaschema area, the task of updating this information is reduced.

Choosing an appropriate block size

Matching the database block size to the operating system allows for a more efficient transfer of data. If you have a block size that is too small, the operating system retrieves more blocks than your request, and while that transfer of additional information might be useful, if the additional information is not used by the application, then the transfer of additional information was wasted. Larger blocks are also generally better because of the way indexes behave. If each block contains more information, then you will require fewer index blocks and fewer index levels to contain the data. Index levels in a B-tree are important. If you can eliminate a level from an index, you can save yourself one additional I/O operation per record request.

Keeping extents sized to eliminate I/O indirection

In an unjournalled file system, I/O indirection happens when a single inode table cannot address all of the addresses within a file. The inode is a mapping of logical to physical addresses in a file. Think of the inode table as a table of contents for the file. In theory, a file can be large (more than 4GB) before indirection occurs, but the conditions need to be perfect. Inode indirection is not an item of concern for journalled file systems.

In the real world, it is possible to see indirection at a file size of 500MB, but on most systems you do not see indirection until 1GB or higher. The penalty for smaller database area extents is fairly low. Generally, you only need to verify that the number of file descriptors (open files) available from the operating system is high enough to support the extents.

Keeping areas small for offline utilities

While many utilities for OpenEdge are available online, there are still some utilities that require you to shut down the database prior to running them. For these utilities, limiting the amount of information per area reduces the amount of downtime needed for the utility to run.

The best example of this is an index rebuild. If you only need to rebuild one index, you still must scan the entire area where the records for that index are stored to ensure you have a pointer to every record. If all of your tables are in one area, this can take a significant amount of time. It is much faster to scan an area where all the records are from one table.

Always have an overflow extent for each area

The last extent of every area, including the primary recovery area, but not the after-image areas, should be variable length. Monitoring storage capacity and growth should eliminate the need to use the variable extent. It is preferable to have it defined in the case that you fill all of your fixed extents and cannot shut down to grow the database. The variable extent allows the database to grow as needed until it is possible to extend the database.

Enabling large files

You should always have large files enabled for your database. However, just as you never want to grow into the variable extent of your data areas, you want to avoid using the large files feature. This feature should be viewed as a safety valve for unanticipated growth.

Large file support allows you to support extent sizes in excess of 1TeraByte (TB), provided that the operating system supports large files. On UNIX, you need to enable each file system where the database resides for this feature to work. In Windows, a file can fill the entire volume. By default, large files are disabled.

Partitioning data

Partitioning data by functional areas is a reasonable way to split your information into small pieces to reduce the size of a given area. This activity allows you to track the expansion of each portion of the application independent of other portions.